package database;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class PurchaseTable extends Table {

	private static final String PURCHASE_INSERT = "INSERT INTO Purchase VALUES (%s, %s, %s, '%s', '%s', %s, %s, %s)";
	private static final String PURCHASE_DELETE = "DELETE FROM Purchase WHERE receiptId=%s";
	private static final String PURCHASE_DISPLAY = "SELECT * FROM Purchase";
	
	private static final String CHECK_RETURN_DATE = "SELECT receiptId FROM Purchase WHERE receiptId = %s AND \"date\" >= %s";
	private static final String PURCHASE_DELIVERY_UPDATE = "UPDATE Purchase SET deliveredDate = %s WHERE expectedDate = %s AND deliveredDate IS NULL";
	private static final String CHECK_UNDELIEVERD_FOR_DATE_QUERY = "SELECT * FROM PURCHASE WHERE sname='Warehouse' AND expectedDate = %s AND deliveredDate IS NULL";
	
	private static final String PURCHASE_UNDELIVERED_QUERY = "SELECT * FROM Purchase WHERE sname='Warehouse' AND expectedDate IS NOT NULL AND deliveredDate IS NULL";
	
	private static final String CUR_RECEIPTID_QUERY = "SELECT receiptId_counter.currval from dual";
	private static final String NEXT_RECEIPTID_QUERY = "SELECT receiptId_counter.nextval FROM dual";
	private static final String PURCHSE_EXISTS_QUERY = "SELECT * FROM Purchase WHERE receiptId=%s";
	
	private static final String DATE_FORMAT = "to_date('%s', 'yyyy-mm-dd')";
	
	public enum Column
	{
		RECEIPT_ID(1, true),
		DATE(2, false),
		CID(3, false),
		SNAME(4, false),
		CARD_NUM(5, false),
		EXPIRE(6, false),
		EXPECTED_DATE(7, false),
		DELIVERED_DATE(8, false);
		
		private int index;
		private boolean isPrimaryKey;
		
		private String[] columnNames = {
				"receiptId",
				"date",
				"cid",
				"sname",
				"cardNum",
				"expire",
				"expectedDate",
				"deliveredDate"};
		
		private Column(int index, boolean isPrimaryKey)  
		{
			this.index = index;
			this.isPrimaryKey = isPrimaryKey;
		}
		
		public int getIndex()
		{
			return index;
		}
		
		public String toString()
		{
			return columnNames[this.ordinal()];
		}

		public boolean isPrimaryKey() {
			return this.isPrimaryKey;
		}
	};
	
	public PurchaseTable()
	{
		super(PURCHASE_INSERT, PURCHASE_DELETE, PURCHASE_DISPLAY, PURCHSE_EXISTS_QUERY);
	}

	@Override
	public List<String> getPrimaryKeyValues(List<String> insertValues) {
		List<String> primaryKeyValues = new ArrayList<String>();
		for(Column col : Column.values())
		{
			if(col.isPrimaryKey())
			{	
				primaryKeyValues.add(insertValues.get(col.ordinal()));
			}
		}				
		
		return primaryKeyValues;
	}
	@Override
	public List<String> getColumnNames() {
		Column columns[] = Column.values();
		
		List<String> columnNames = new ArrayList<String>(columns.length);
		for( Column col : columns)
		{
			columnNames.add(col.ordinal(), col.toString());
		}
		return columnNames;
	}

	@Override
	public List<String> getPrimaryKeyColumnNames() {
		Column columns[] = Column.values();
		
		List<String> primaryKeyColumnNames = new ArrayList<String>(columns.length);
		for( Column col : columns)
		{
			if(col.isPrimaryKey())
			{
				primaryKeyColumnNames.add(col.ordinal(), col.toString());
			}
		}
		return primaryKeyColumnNames;
	}
	
	public void inStorePurchase(String date, String sname, String card, String expire) throws SQLException 
	{
		List<String> purchaseTuple = new ArrayList<String>(Column.values().length);
		String expiryDate = (expire == null) ? "NULL" : String.format(DATE_FORMAT, expire);
		
		purchaseTuple.add(Column.RECEIPT_ID.ordinal(), String.valueOf(getNextReceiptId()));
		purchaseTuple.add(Column.DATE.ordinal(), String.format(DATE_FORMAT, date));
		purchaseTuple.add(Column.CID.ordinal(), "NULL");
		purchaseTuple.add(Column.SNAME.ordinal(), sname);
		purchaseTuple.add(Column.CARD_NUM.ordinal(), card);
		purchaseTuple.add(Column.EXPIRE.ordinal(), expiryDate);
		purchaseTuple.add(Column.EXPECTED_DATE.ordinal(), "NULL");
		purchaseTuple.add(Column.DELIVERED_DATE.ordinal(), "NULL");
		
		insert(purchaseTuple);
	}
	
	public void onlinePurchase(String cid, String date, String card, String expiryDate, String expectedDate) throws SQLException
	{
		List<String> purchaseTuple = new ArrayList<String>(Column.values().length);
		purchaseTuple.add(Column.RECEIPT_ID.ordinal(), String.valueOf(getNextReceiptId()));
		purchaseTuple.add(Column.DATE.ordinal(), String.format(DATE_FORMAT, date));
		purchaseTuple.add(Column.CID.ordinal(), "'"+cid+"'");
		purchaseTuple.add(Column.SNAME.ordinal(), "Warehouse");
		purchaseTuple.add(Column.CARD_NUM.ordinal(), card);
		purchaseTuple.add(Column.EXPIRE.ordinal(), String.format(DATE_FORMAT, expiryDate));
		purchaseTuple.add(Column.EXPECTED_DATE.ordinal(), String.format(DATE_FORMAT, expectedDate));
		purchaseTuple.add(Column.DELIVERED_DATE.ordinal(), "NULL");
		
		insert(purchaseTuple);
	}
	
	public int getCurrentReceiptId() throws SQLException {
		int curReceiptId = -1;
		
		ResultSet rs = dbCon.executeQuery(CUR_RECEIPTID_QUERY);
		if(rs != null)
		{
			if(rs.next())
			{
				curReceiptId = rs.getInt(1);
			}
			
			rs.close();
		}
				
		return curReceiptId;
	}
	
	public boolean checkReturnDate(String receiptId, Date curDate) throws SQLException {
		long fifteenDaysOldEpoch = 1296000000;
		Date dateToCheck = new Date(curDate.getTime() - fifteenDaysOldEpoch);
        String dateToCheckString = String.format(DATE_FORMAT, dateToCheck.toString());
		
		String fmtdQuery = String.format(CHECK_RETURN_DATE, receiptId, dateToCheckString);
		ResultSet rs = dbCon.executeQuery(fmtdQuery);
		if(rs != null)
		{
			if(rs.next())
			{
				return true;
			}
			
			rs.close();
		}
	
		return false;
	}
	
	public void returnPurchase(String receiptId) throws SQLException 
	{
		List<String> returnTuple = new ArrayList<String>(Column.values().length);
		returnTuple.add(Column.RECEIPT_ID.ordinal(), receiptId);
		
		delete(returnTuple);
	}
	
	public void updateDeliveredDates(Date expectedDate, Date deliveredDate) throws SQLException {
		String expectedDateString = String.format(DATE_FORMAT, expectedDate.toString());
		String deliveredDateString = String.format(DATE_FORMAT, deliveredDate.toString());
		
		String fmtdQuery = String.format(PURCHASE_DELIVERY_UPDATE, expectedDateString, deliveredDateString);
		dbCon.executeStatement(fmtdQuery);
	}
	
	public boolean checkForUndelieveredOrders(Date date) throws SQLException
	{
		boolean undeliveredOrders = false;
		
		String dateString = String.format(DATE_FORMAT, date.toString());		
		String fmtdQuery = String.format(CHECK_UNDELIEVERD_FOR_DATE_QUERY, dateString);
		
		ResultSet rs = dbCon.executeQuery(fmtdQuery);
		
		if(rs != null)
		{
			undeliveredOrders = rs.next();
		}
		
		return undeliveredOrders;
	}
	
	public int getNumOutstandingOnlineOrders() throws SQLException
	{
		int numOustandingOrders = 0;
		
		// simply count the number of results returned by the undelivered query
		ResultSet rs = dbCon.executeQuery(PURCHASE_UNDELIVERED_QUERY);
		while(rs.next())
		{
			numOustandingOrders++;
		}
		
		rs.close();
		
		return numOustandingOrders;
	}
	
	private int getNextReceiptId() throws SQLException
	{
		int receiptId = 0;
		
		ResultSet rs = dbCon.executeQuery(NEXT_RECEIPTID_QUERY);
		if(rs != null)
		{
			if(rs.next())
			{
				receiptId = rs.getInt(1);
			}
			
			rs.close();
		}
		
		return receiptId;
	}
}
